package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import model.BorrowedItem;
import model.Item;
import model.ItemType;

public class ItemManager
{

    public List<Item> filterItems(String title, String[] types, String owner)
    {
	StringBuilder inClause = new StringBuilder("(");
	if (types == null || types.length == 0)
	{
	    ItemType[] itemTypes = ItemType.values();
	    for (int i = 0; i < itemTypes.length; i++)
	    {
		inClause.append("'" + itemTypes[i].getName() + "'");
		if (i < itemTypes.length - 1)
		{
		    inClause.append(",");
		}
	    }
	} else
	{
	    for (int i = 0; i < types.length; i++)
	    {
		inClause.append("'" + types[i] + "'");
		if (i < types.length - 1)
		{
		    inClause.append(",");
		}
	    }
	}
	inClause.append(")");
	List<Item> result = new ArrayList<Item>();
	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    String query = "SELECT * FROM Items WHERE UPPER(title) LIKE '%"
		    + title.toUpperCase() + "%' " + "AND type IN " + inClause;
	    if (owner != null && !owner.equals(""))
	    {
		query += " AND owner='" + owner + "'";
	    }
	    query += " ORDER BY item_id";
	    PreparedStatement ps = conn.prepareStatement(query);
	    ResultSet resultSet = ps.executeQuery();
	    while (resultSet.next())
	    {
		Item currentItem = populateItem(resultSet);
		result.add(currentItem);
	    }
	    ConnectionManager.getInstance().returnConnection(conn);
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	}
	return result;
    }

    public List<Item> fetchOwnFreeItems(String username)
    {
	List<Item> result = new ArrayList<Item>();

	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn
		    .prepareStatement("SELECT * FROM Items WHERE owner='"
			    + username + "' AND state='0' ORDER BY item_id ASC");
	    ResultSet resultSet = ps.executeQuery();
	    while (resultSet.next())
	    {
		Item currentItem = populateItem(resultSet);
		result.add(currentItem);
	    }
	    ConnectionManager.getInstance().returnConnection(conn);
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	}
	return result;
    }

    private List<BorrowedItem> fetchBorrowedItems(String query, String username)
    {
	List<BorrowedItem> result = new ArrayList<BorrowedItem>();
	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn.prepareStatement(query);
	    ps.setString(1, username);
	    ResultSet resultSet = ps.executeQuery();
	    while (resultSet.next())
	    {
		BorrowedItem currentItem = populateBorrowedItem(resultSet);
		result.add(currentItem);
	    }
	    ConnectionManager.getInstance().returnConnection(conn);
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	}
	return result;
    }

    public List<BorrowedItem> fetchBorrowedItemsBySelf(String username)
    {
	String query = "SELECT * FROM items i, borrowed_items b "
		+ "WHERE i.item_id = b.item_id AND b.borrower=? AND i.state=1 AND b.return_date IS NULL "
		+ "ORDER BY i.item_id ASC";
	return fetchBorrowedItems(query, username);
    }

    public List<BorrowedItem> fetchOwnBorrowedItems(String username)
    {
	String query = "SELECT * FROM items i, borrowed_items b "
		+ "WHERE i.item_id = b.item_id AND i.owner=? AND i.state=1 AND b.return_date IS NULL "
		+ "ORDER BY i.item_id ASC";
	return fetchBorrowedItems(query, username);
    }

    private BorrowedItem populateBorrowedItem(ResultSet resultSet)
	    throws SQLException
    {
	BorrowedItem borrowedItem = new BorrowedItem();
	Item item = populateItem(resultSet);
	borrowedItem.setItem(item);
	borrowedItem.setId(resultSet.getInt("id"));
	borrowedItem.setBorrower(resultSet.getString("borrower"));
	borrowedItem.setBorrowDate(resultSet.getString("borrow_date"));
	borrowedItem.setExpectedReturnDate(resultSet
		.getString("expected_return_date"));
	return borrowedItem;
    }

    public List<Item> fetchAllAvailableItems()
    {
	List<Item> result = new ArrayList<Item>();
	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn
		    .prepareStatement("SELECT * FROM Items WHERE state=0");
	    ResultSet resultSet = ps.executeQuery();
	    while (resultSet.next())
	    {
		Item currentItem = populateItem(resultSet);
		result.add(currentItem);
	    }

	    ConnectionManager.getInstance().returnConnection(conn);
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	}
	return result;
    }

    public Item fetchItemById(int Id)
    {
	Item result = null;
	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn
		    .prepareStatement("SELECT * FROM Items WHERE item_id=" + Id);
	    ResultSet resultSet = ps.executeQuery();
	    assert resultSet.getFetchSize() <= 1 : "The database should not contain multiple Items"
		    + " with the same id.";

	    if (resultSet.next())
	    {
		Item currentItem = populateItem(resultSet);
		result = currentItem;
	    }

	    ConnectionManager.getInstance().returnConnection(conn);
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	}
	return result;
    }

    public boolean returnItem(int borrowedItemId) throws SQLException
    {
	// boolean result = false;
	int updated = 0;
	Connection conn = ConnectionManager.getInstance().getConnection();
	if (conn == null)
	{
	    return false;
	}
	try
	{
	    conn.setAutoCommit(false);
	    String query = "UPDATE Borrowed_Items SET return_date=CURRENT_DATE WHERE id=?";
	    PreparedStatement st = conn.prepareStatement(query);
	    st.setInt(1, borrowedItemId);
	    updated = st.executeUpdate();
	    if (updated > 0)
	    {
		query = "SELECT item_id FROM Borrowed_Items WHERE id=?";
		st = conn.prepareStatement(query);
		st.setInt(1, borrowedItemId);
		ResultSet resultSet = st.executeQuery();
		int itemId = -1;
		while (resultSet.next())
		{
		    itemId = resultSet.getInt("item_id");
		}
		if (itemId > -1)
		{
		    query = "UPDATE Items SET state=0 WHERE item_id=?";
		    st = conn.prepareStatement(query);
		    st.setInt(1, itemId);
		    updated = st.executeUpdate();
		}
	    }
	    conn.commit();
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	    conn.rollback();
	    updated = 0;
	}
	finally
	{
	    conn.setAutoCommit(true);
	    ConnectionManager.getInstance().returnConnection(conn);
	}
	return updated == 1;
    }

    private Item populateItem(ResultSet resultSet) throws SQLException
    {
	int id = resultSet.getInt("item_id");
	String title = resultSet.getString("title");
	String author = resultSet.getString("author");
	String publishDate = resultSet.getString("publish_date");
	String owner = resultSet.getString("owner");
	String type = resultSet.getString("type");
	int state = resultSet.getInt("state");
	int maxPeriod = resultSet.getInt("max_period");

	Item item = new Item();
	item.setId(id);
	item.setTitle(title);
	item.setAuthor(author);
	item.setPublishDate(publishDate);
	item.setOwner(owner);
	item.setType(type);
	item.setState(state);
	item.setMaxPeriod(maxPeriod);
	return item;
    }

    public boolean insertItem(String title, String author, String publishDate,
	    String owner, String type, int maxPeriod)
    {
	String query = "INSERT INTO Items(title, author, publish_date, "
		+ "owner, type, max_period) VALUES ('" + title + "', '"
		+ author + "', '" + publishDate + "', '" + owner + "', '"
		+ type + "', " + maxPeriod + ")";
	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn.prepareStatement(query);
	    int rowsAffected = ps.executeUpdate();
	    ConnectionManager.getInstance().returnConnection(conn);
	    return rowsAffected == 1;
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	    return false;
	}
    }

    public boolean updateItem(int id, String title, String author,
	    String publishDate, String type, int maxPeriod)
    {
	
	String query = "UPDATE Items SET title = ?, author = ?, publish_date = ?, "
		+ "type = ?, max_period = ? WHERE item_id=?";


	try
	{
	    Connection conn = ConnectionManager.getInstance().getConnection();
	    PreparedStatement ps = conn.prepareStatement(query);
	    ps.setString(1, title);
	    ps.setString(2, author);
	    ps.setString(3, publishDate);
	    ps.setString(4, type);
	    ps.setInt(5, maxPeriod);
	    ps.setInt(6, id);
	    int rowsAffected = ps.executeUpdate();
	    ConnectionManager.getInstance().returnConnection(conn);
	    return rowsAffected == 1;
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	    return false;
	}
    }

    public boolean borrowItem(int itemId, String borrower, String returnDate)
	    throws SQLException
    {
	// boolean result = false;
	int updated = 0;
	Connection conn = ConnectionManager.getInstance().getConnection();
	if (conn == null)
	{
	    return false;
	}
	try
	{
	    conn.setAutoCommit(false);
	    String query = "UPDATE Items SET state=1 WHERE item_id=?";
	    PreparedStatement st = conn.prepareStatement(query);
	    st.setInt(1, itemId);
	    updated = st.executeUpdate();
	    if (updated > 0)
	    {
		query = "INSERT INTO Borrowed_Items(BORROWER, ITEM_ID, EXPECTED_RETURN_DATE)"
			+ "VALUES(?,?,?)";
		st = conn.prepareStatement(query);
		st.setString(1, borrower);
		st.setInt(2, itemId);
		st.setString(3, returnDate);
		updated = st.executeUpdate();

	    }
	    conn.commit();
	}
	catch (SQLException e)
	{
	    System.err.println(e.getMessage());
	    conn.rollback();
	    updated = 0;
	}
	finally
	{
	    conn.setAutoCommit(true);
	    ConnectionManager.getInstance().returnConnection(conn);
	}
	return updated == 1;
    }
}